﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class THELOAISACHDAO
    {
        public string laytentheloai(string matheloai)
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT TenTheLoai FROM THE_LOAI_SACH WHERE MaLoaiSach = '" + matheloai + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            object temp = cmd.ExecuteScalar();
            string ten = temp.ToString();
            conn.Close();
            conn.Dispose();
            return ten;
        }

        public int update(string strquery, DataTable datatable)
        {
            int effrow = 0;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strquery;
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            cmd.Connection = conn;
            SqlDataAdapter sqldataadapter = new SqlDataAdapter(cmd);
            SqlCommandBuilder sqlcomandbuilder = new SqlCommandBuilder(sqldataadapter);
            effrow = sqldataadapter.Update(datatable);
            conn.Close();
            conn.Dispose();
            sqldataadapter.Dispose();            
            return effrow;
        }
        public int Themtheloai(THELOAISACHDTO tlsDTO)
        {
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql1 = "SELECT * FROM THE_LOAI_SACH WHERE MaLoaiSach = '" + tlsDTO.Maloaisach + "' OR TenTheLoai = N'" + tlsDTO.Tentheloai + "'";
            SqlCommand cmd1 = new SqlCommand(sql1, conn);
            int kq1 = cmd1.ExecuteNonQuery();
            if (kq1 > 0)
            {
                conn.Close();
                conn.Dispose();
                return -1;
            }
            else
            {
                try
                {
                    string sql = "INSERT INTO THE_LOAI_SACH VALUES(@MTL,@TTL)";
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlParameter sqlparameter = new SqlParameter("@MTL", System.Data.SqlDbType.NChar, 3);
                    sqlparameter.Value = tlsDTO.Maloaisach;
                    cmd.Parameters.Add(sqlparameter);
                    sqlparameter = new SqlParameter("@TTL", System.Data.SqlDbType.NVarChar, 50);
                    sqlparameter.Value = tlsDTO.Tentheloai;
                    cmd.Parameters.Add(sqlparameter);
                    int kq = cmd.ExecuteNonQuery();
                    conn.Close();
                    conn.Dispose();
                    if (kq > 0)
                    {
                        BIENTHELOAISACHDAO btlsDAO = new BIENTHELOAISACHDAO();
                        btlsDAO.thembien(tlsDTO);
                    }
                    return kq;
                }
                catch
                {
                    conn.Close();
                    conn.Dispose();
                    return -1;
                }
            }
        }

        public DataTable Loadtheloaisach()
        {
            DataTable datatble = new DataTable();
            SqlConnection conn = DATAPROVIDER.ConnectSQL();
            string sql = "SELECT * FROM THE_LOAI_SACH";
            SqlDataAdapter sqldataadapter = new SqlDataAdapter(sql, conn);
            sqldataadapter.Fill(datatble);
            conn.Close();
            conn.Dispose();
            sqldataadapter.Dispose();
            return datatble;
        }
    }
}
